﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using OgilvyOne.IDAL;
using System.Data;
using OgilvyOne.Model;

namespace OgilvyOne.SqlServerDAL
{
    public partial class QuestionsManageDal : IQuestionsManage
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        public QuestionsManageDal()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from COFCO_FAQ");
            strSql.Append(" where FAQ_ID=@FAQ_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@FAQ_ID", SqlDbType.Int,4)
};
            parameters[0].Value = id;

            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(QuestionsManageModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into COFCO_FAQ(");
            strSql.Append("FAQ_TYPE,FAQ_NAME,FAQ_ANSWER,STATE,ADD_TIME,USER_ID,FAQ_BUSINESS_TYPE)");
            strSql.Append(" values (");
            strSql.Append("@FAQ_TYPE,@FAQ_NAME,@FAQ_ANSWER,@STATE,@ADD_TIME,@USER_ID,@FAQ_BUSINESS_TYPE)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@FAQ_TYPE", SqlDbType.Int,10),
					new SqlParameter("@FAQ_NAME", SqlDbType.VarChar,200),
					new SqlParameter("@FAQ_ANSWER", SqlDbType.VarChar,10000),
					new SqlParameter("@STATE", SqlDbType.Int,4),
					new SqlParameter("@ADD_TIME", SqlDbType.DateTime),
                    new SqlParameter("@USER_ID", SqlDbType.Int,4),
                    new SqlParameter("@FAQ_BUSINESS_TYPE", SqlDbType.Int,20)
					};
            parameters[0].Value = model.Faqtype;
            parameters[1].Value = model.Faqname;
            parameters[2].Value = model.Faqanswer;
            parameters[3].Value = model.State;
            parameters[4].Value = model.Addtime;
            parameters[5].Value = model.Userid;
            parameters[6].Value = model.Faqbusinesstype;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(QuestionsManageModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update COFCO_FAQ set ");
            strSql.Append("FAQ_TYPE=@FAQ_TYPE,");
            strSql.Append("FAQ_NAME=@FAQ_NAME,");
            strSql.Append("FAQ_ANSWER=@FAQ_ANSWER,");
            strSql.Append("STATE=@STATE,");
            strSql.Append("ADD_TIME=@ADD_TIME,");
            strSql.Append("FAQ_BUSINESS_TYPE=@FAQ_BUSINESS_TYPE");
            strSql.Append(" where FAQ_ID=@FAQ_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@FAQ_TYPE", SqlDbType.Int,10),
					new SqlParameter("@FAQ_NAME", SqlDbType.VarChar,200),
					new SqlParameter("@FAQ_ANSWER", SqlDbType.VarChar,4000),
					new SqlParameter("@STATE", SqlDbType.Int,4),
					new SqlParameter("@ADD_TIME", SqlDbType.DateTime),
                    new SqlParameter("@FAQ_BUSINESS_TYPE", SqlDbType.Int,20),
					new SqlParameter("@FAQ_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = int.Parse(model.Faqtype);
            parameters[1].Value = model.Faqname;
            parameters[2].Value = model.Faqanswer;
            parameters[3].Value = model.State;
            parameters[4].Value = model.Addtime;
            parameters[5].Value = model.Faqbusinesstype;
            parameters[6].Value = model.Faqid;

            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int FAQ_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_FAQ ");
            strSql.Append(" where FAQ_ID=@FAQ_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@FAQ_ID", SqlDbType.Int,4)
};
            parameters[0].Value = FAQ_ID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string FAQ_IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_FAQ ");
            strSql.Append(" where FAQ_ID in (" + FAQ_IDlist + ")  ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public QuestionsManageModel GetModel(int FAQ_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FAQ_ID,FAQ_TYPE,FAQ_NAME,FAQ_ANSWER,STATE,ADD_TIME,USER_ID,FAQ_BUSINESS_TYPE from COFCO_FAQ ");
            strSql.Append(" where FAQ_ID=@FAQ_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@FAQ_ID", SqlDbType.Int,4)
                };
            parameters[0].Value = FAQ_ID;

            QuestionsManageModel model = new QuestionsManageModel();
            using (SqlDataReader dr = SQLHelper.ExecuteReader(connstr, CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        model.Faqid = dr.GetInt32(0);
                    }
                    if (!dr.IsDBNull(1))
                    {
                        model.Faqtype = dr.GetInt32(1).ToString();
                    }
                    if (!dr.IsDBNull(2))
                    {
                        model.Faqname = dr.GetString(2);
                    }
                    if (!dr.IsDBNull(3))
                    {
                        model.Faqanswer = dr.GetString(3);
                    }
                    if (!dr.IsDBNull(4))
                    {
                        model.State = dr.GetInt32(4);
                    }
                    if (!dr.IsDBNull(5))
                    {
                        model.Addtime = dr.GetDateTime(5);
                    }
                    if (!dr.IsDBNull(6))
                    {
                        model.Userid = dr.GetInt32(6);
                    }
                    if (!dr.IsDBNull(7))
                    {
                        model.Faqbusinesstype = dr.GetInt32(7);
                    }
                }
                return model;
            }

        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FAQ_ID,FAQ_TYPE,FAQ_NAME,FAQ_ANSWER,STATE,ADD_TIME,FAQ_BUSINESS_TYPE,USER_ID ");
            strSql.Append(" FROM COFCO_FAQ ");
            if (strWhere != null & strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" FAQ_ID,FAQ_TYPE,FAQ_NAME,FAQ_ANSWER,STATE,ADD_TIME,FAQ_BUSINESS_TYPE,USER_ID ");
            strSql.Append(" FROM COFCO_FAQ ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得当前页码指定的问题数据
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <returns></returns>
        public List<QuestionsManageModel> GetPagedQuestions(int pageIndex, int pageSize, out int rowCount)
        {
            //问题对象集合(从数据库查到的问题表行数据 都转成 实体对象 添加到此集合)
            List<QuestionsManageModel> list = null;
            //创建连接通道
            SqlConnection conn = new SqlConnection(connstr);
            //创建适配器对象(它会自动打开和关闭连接通道)
            SqlDataAdapter da = new SqlDataAdapter("up_GetPagedData", conn);
            //告诉 适配器 要执行的是 执行存储过程
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter[] paras = { 
                                   new SqlParameter("@pageSize", SqlDbType.Int),
                                   new SqlParameter("@pageIndex", SqlDbType.Int),
                                   new SqlParameter("@rowCount", SqlDbType.Int)
                                   };
            paras[0].Value = pageSize;
            paras[1].Value = pageIndex;
            //设置第三个参数为输出类型的参数 (由存储过程输出一个总行数给此参数)
            paras[2].Direction = ParameterDirection.Output;
            //将 参数数组 加入适配器要执行的查询命令对象 的参数集合中
            da.SelectCommand.Parameters.AddRange(paras);
            DataTable dt = new DataTable();
            //数据库存储过程 查询到的 结果表 赋给了 程序中的 数据表对象dt(程序的临时仓库)
            da.Fill(dt);
            //将数据库存储过程返回的输出参数的值 赋给 方法的 输出变量 rowCount
            rowCount = (int)da.SelectCommand.Parameters[2].Value;
            if (dt.Rows.Count > 0)
            {
                list = new List<QuestionsManageModel>();
                foreach (DataRow dr in dt.Rows)
                {
                    QuestionsManageModel model = new QuestionsManageModel();
                    Dr2Model(dr, model);
                    list.Add(model);
                }
            }
            return list;
        }
        #region 将数据行 转成 实体对象
        /// <summary>
        /// 将数据行 转成 实体对象
        /// </summary>
        /// <param name="dr">数据行</param>
        /// <param name="model">实体对象</param>
        void Dr2Model(DataRow dr, QuestionsManageModel model)
        {

            if (dr.Table.Columns.Contains("FAQ_ID") && dr["FAQ_ID"].ToString() != "")
            {
                model.Faqid = int.Parse(dr["FAQ_ID"].ToString());
            }
            if (dr["FAQ_TYPE"].ToString() != "")
            {
                model.Faqtype = dr["FAQ_TYPE"].ToString();
            }
            if (dr["FAQ_NAME"].ToString() != "")
            {
                model.Faqname = dr["FAQ_NAME"].ToString();
            }
            if (dr["FAQ_ANSWER"].ToString() != "")
            {
                model.Faqanswer = dr["FAQ_ANSWER"].ToString();
            }
            if (dr["STATE"].ToString() != "")
            {
                model.State = int.Parse(dr["STATE"].ToString());
            }
            if (dr["ADD_TIME "].ToString() != "")
            {
                model.Addtime = Convert.ToDateTime(dr["ADD_TIME"].ToString());
            }
            if (dr.Table.Columns.Contains("USER_ID") && dr["USER_ID"].ToString() != "")
            {
                model.Userid = int.Parse(dr["USER_ID"].ToString());
            }
        }
        #endregion

        #endregion  Method
    }
}
